﻿using System.Collections.Generic;
using Excel;
using System.Data;
using System.IO;
using Newtonsoft.Json;
using System.Text;
using System.Reflection;
using System;

public class ExcelUtility
{

	/// <summary>
	/// 表格数据集合
	/// </summary>
	private DataSet mResultSet;

	/// <summary>
	/// 构造函数
	/// </summary>
	/// <param name="excelFile">Excel file.</param>
	public ExcelUtility (string excelFile)
	{
		FileStream mStream = File.Open (excelFile, FileMode.Open, FileAccess.Read);
		IExcelDataReader mExcelReader = ExcelReaderFactory.CreateOpenXmlReader (mStream);
		mResultSet = mExcelReader.AsDataSet ();
	}
			
	/// <summary>
	/// 转换为实体类列表
	/// </summary>
	public List<T> ConvertToList<T> ()
	{
		//判断Excel文件中是否存在数据表
		if (mResultSet.Tables.Count < 1)
			return null;
		//默认读取第一个数据表
		DataTable mSheet = mResultSet.Tables [0];
			
		//判断数据表内是否存在数据
		if (mSheet.Rows.Count < 1)
			return null;

		//读取数据表行数和列数
		int rowCount = mSheet.Rows.Count;
		int colCount = mSheet.Columns.Count;
				
		//准备一个列表以保存全部数据
		List<T> list = new List<T> ();
				
		//读取数据
		for (int i=1; i<rowCount; i++) 
		{
			//创建实例
			Type t = typeof(T);
			ConstructorInfo ct = t.GetConstructor (System.Type.EmptyTypes);
			T target = (T)ct.Invoke (null);
			for (int j=0; j<colCount; j++) 
			{
				//读取第1行数据作为表头字段
				string field = mSheet.Rows [0] [j].ToString ();
				object value = mSheet.Rows [i] [j];
				//设置属性值
				SetTargetProperty (target, field, value);
			}
					
			//添加至列表
			list.Add (target);
		}
				
		return list;
	}

	/// <summary>
	/// 转换为Json
	/// </summary>
	/// <param name="JsonPath">Json文件路径</param>
	/// <param name="Header">表头行数</param>    
	public void ConvertToJson (string JsonPath, Encoding encoding,int fieldIndex=0,int startIndex=1)
	{
		//判断Excel文件中是否存在数据表
		if (mResultSet.Tables.Count < 1)
			return;

        foreach (DataTable mSheet in mResultSet.Tables)
        {            
            //判断数据表内是否存在数据
            if (mSheet.Rows.Count < 1)
                return;

            //读取数据表行数和列数
            int rowCount = mSheet.Rows.Count;
            int colCount = mSheet.Columns.Count;

            //准备一个列表存储整个表的数据
            List<Dictionary<string, object>> table = new List<Dictionary<string, object>>();

            //读取数据
            for (int i = startIndex; i < rowCount; i++)
            {
                //排除空数据
                if (string.IsNullOrEmpty(mSheet.Rows[i][0].ToString()))
                {
                    break;
                }
                //准备一个字典存储每一行的数据
                Dictionary<string, object> row = new Dictionary<string, object>();
                for (int j = 0; j < colCount; j++)
                {
                                   
                    //读取第1行数据作为表头字段
                    string[] file_type = mSheet.Rows[fieldIndex][j].ToString().Split('|');
                    if (file_type.Length > 1)
                    {
                        string field = file_type[0];
                        string type = mSheet.Rows[fieldIndex][j].ToString().Split('|')[1];
                        switch (type)
                        {
                            case "Int":
                                if (string.IsNullOrEmpty(mSheet.Rows[i][j].ToString()))
                                {
                                    row[field] = mSheet.Rows[i][j];
                                }
                                else
                                {
                                    row[field] = Convert.ToInt32(mSheet.Rows[i][j]);
                                }
                                break;
                            case "String":
                                row[field] = mSheet.Rows[i][j].ToString();
                                break;
                            case "Float":
                                if (string.IsNullOrEmpty(mSheet.Rows[i][j].ToString()))
                                {
                                    row[field] = mSheet.Rows[i][j];
                                }
                                else
                                {
                                    row[field] = Convert.ToSingle(mSheet.Rows[i][j]);
                                }
                                break;
                            default:
                                row[field] = mSheet.Rows[i][j];
                                break;
                        }
                    }
                    else
                    {
                        string field = file_type[0];
                        row[field] = mSheet.Rows[i][j];
                    }                                                 
                }
                //添加到表数据中
                table.Add(row);
            }

            //生成Json字符串
            string json = JsonConvert.SerializeObject(table,Formatting.Indented);
            //UnityEngine.Debug.Log("Json:" + json);
            //写入文件
            JsonPath = JsonPath.Substring(0,JsonPath.LastIndexOf('/'))+"/"+mSheet.TableName+".json";
            using (FileStream fileStream = new FileStream(JsonPath, FileMode.Create, FileAccess.Write))
            {
                using (TextWriter textWriter = new StreamWriter(fileStream, encoding))
                {
                    textWriter.Write(json);
                }
            }
        }

		
	}

	/// <summary>
	/// 导出为Xml
	/// </summary>
	public void ConvertToXml (string XmlFile, int fieldIndex = 0, int startIndex = 1)
	{
		//判断Excel文件中是否存在数据表
		if (mResultSet.Tables.Count < 1)
			return;

        foreach (DataTable mSheet in mResultSet.Tables)
        {

            //判断数据表内是否存在数据
            if (mSheet.Rows.Count < 1)
                return;

            //读取数据表行数和列数
            int rowCount = mSheet.Rows.Count;
            int colCount = mSheet.Columns.Count;

            //创建一个StringBuilder存储数据
            StringBuilder stringBuilder = new StringBuilder();
            //创建Xml文件头
            stringBuilder.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
            stringBuilder.Append("\r\n");
            //创建根节点
            stringBuilder.Append("<Table>");
            stringBuilder.Append("\r\n");
            //读取数据
            for (int i = startIndex; i < rowCount; i++)
            {

                //创建子节点
                stringBuilder.Append("  <Row>");
                stringBuilder.Append("\r\n");
                for (int j = 0; j < colCount; j++)
                {              
                    
                    string[] file_type = mSheet.Rows[fieldIndex][j].ToString().Split('|');
                    Dictionary<string, object> row = new Dictionary<string, object>();
                    if (file_type.Length > 1)
                    {
                        string field = file_type[0];
                        string type = mSheet.Rows[fieldIndex][j].ToString().Split('|')[1];
                        switch (type)
                        {
                            case "Int":

                                stringBuilder.Append("   <" + field + ">");
                                stringBuilder.Append(Convert.ToInt32(mSheet.Rows[i][j]));
                                stringBuilder.Append("</" + field + ">");
                                stringBuilder.Append("\r\n");            
                                
                                break;
                            case "String":
                                stringBuilder.Append("   <" + field + ">");
                                stringBuilder.Append(mSheet.Rows[i][j].ToString());
                                stringBuilder.Append("</" + field + ">");
                                stringBuilder.Append("\r\n");
                                break;
                            case "Float":
                                if (string.IsNullOrEmpty(mSheet.Rows[i][j].ToString()))
                                {
                                    row[field] = mSheet.Rows[i][j];

                                    stringBuilder.Append("   <" + field + ">");
                                    stringBuilder.Append(row[field]);
                                    stringBuilder.Append("</" + field + ">");
                                    stringBuilder.Append("\r\n");
                                }
                                else
                                {
                                    row[field] = Convert.ToSingle(mSheet.Rows[i][j]);

                                    stringBuilder.Append("   <" + field + ">");
                                    stringBuilder.Append(row[field]);
                                    stringBuilder.Append("</" + field + ">");
                                    stringBuilder.Append("\r\n");
                                }
                                break;
                            default:
                                row[field] = mSheet.Rows[i][j];

                                stringBuilder.Append("   <" + field + ">");
                                stringBuilder.Append(row[field]);
                                stringBuilder.Append("</" + field + ">");
                                stringBuilder.Append("\r\n");
                                break;
                        }
                    }
                    else
                    {
                        string field = file_type[0];
                        row[field] = mSheet.Rows[i][j];

                        stringBuilder.Append("   <" + field + ">");
                        stringBuilder.Append(row[field]);
                        stringBuilder.Append("</" + field + ">");
                        stringBuilder.Append("\r\n");
                    }
                }
                //使用换行符分割每一行
                stringBuilder.Append("  </Row>");
                stringBuilder.Append("\r\n");
            }
            //闭合标签
            stringBuilder.Append("</Table>");

            XmlFile = XmlFile.Substring(0, XmlFile.LastIndexOf('/')) + "/" + mSheet.TableName + ".xml";

            //写入文件
            using (FileStream fileStream = new FileStream(XmlFile, FileMode.Create, FileAccess.Write))
            {
                using (TextWriter textWriter = new StreamWriter(fileStream, Encoding.GetEncoding("utf-8")))
                {
                    textWriter.Write(stringBuilder.ToString());
                }
            }
        }          
	}

	/// <summary>
	/// 设置目标实例的属性
	/// </summary>
	private void SetTargetProperty (object target, string propertyName, object propertyValue)
	{
		//获取类型
		Type mType = target.GetType ();
		//获取属性集合
		PropertyInfo[] mPropertys = mType.GetProperties ();
		foreach (PropertyInfo property in mPropertys) {
			if (property.Name == propertyName) {
				property.SetValue (target, Convert.ChangeType (propertyValue, property.PropertyType), null);
			}
		}
	}
}

